<?php
class QSupplier
{	
	public function __construct($p_strCountry)
	{
		$this->_country	= $p_strCountry;
		$this->_table	= $this->_country.'_suppliers';
		
		$this->_db		= new Common_Db('wms_'.$this->_country . '_bob');

	}
	
	public function get_oms($p_strSku)
	{
		$db		= new Common_Db('wms_'.$this->_country);
		$strQuery	= '
			SELECT a.sku , c.name `supplier`
			FROM oms_live.ims_product  a 
				INNER JOIN oms_live.ims_supplier_product b On a.id_product = b.fk_product
				INNER JOIN oms_live.ims_supplier c On b.fk_supplier = c.id_supplier
			WHERE LOWER(a.sku) IN ("'.strtolower($p_strSku).'")
		';
		return $db->fetchAll($strQuery);
	}
	
	private function _whereSearch($p_arrParams)
	{
		$strQuery		= null;
		if(isset($p_arrParams['name']) && $p_arrParams['name'] ){
			$strQuery	= $strQuery.' AND LOWER(s.name) LIKE "'. strtolower($p_arrParams['name']) . '%" Or s.id LIKE "' . $p_arrParams['name'] . '"' ;
		};
		
		if(isset($p_arrParams['weekday']) && $p_arrParams['weekday'] ){
			$strQuery	= $strQuery.' AND LOWER(s.weekday)="'.strtolower($p_arrParams['weekday']).'"';
		};
	
		return $strQuery;
	}
	
	public function total($p_arrParams)
	{
		$strQuery	= 'SELECT COUNT(s.id) `total` FROM '.$this->_table .' s' ;
		$strQuery	= $strQuery. ' WHERE 1=1 ';
		$strQuery	= $strQuery. $this->_whereSearch($p_arrParams);
		$db			= Zend_Registry::get('db');
		$arrResult	= $db->fetchAll($strQuery);
		$db->closeConnection();
		return isset($arrResult[0]['total']) ? $arrResult[0]['total'] : 0;
	}
	
	public function search($p_intOffset, $p_mixLimit, $p_arrParams)
	{
		$strQuery	= '
			SELECT *  FROM '.$this->_table . ' s ' ;
		$strQuery	= $strQuery. ' WHERE 1=1';
		$strQuery	= $strQuery. $this->_whereSearch($p_arrParams);
				
		$strQuery	= $strQuery .' ORDER BY  name  ';
		
		if($p_mixLimit!='all'){
			$strQuery	= $strQuery.' LIMIT '.intval($p_intOffset).','.intval($p_mixLimit);
		}

		$db			= Zend_Registry::get('db');
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function getSupplierInfor()
	{
		$strQuery	=  'SELECT i.id_catalog_supplier as id, i.name as name, i.status as status FROM catalog_supplier i
						group by i.id_catalog_supplier;'; 				
		$arrData = $this->_db->fetchAll($strQuery); 	 
		//Get supplier information
		
		$arrDataSendEmailNew = array();
		$arrDataSendEmailRemove = array();
		if($arrData)
		{	foreach ($arrData as $data)
			{
				$p_arrParams =array(
					'id' => $data['id'],
					'name' =>  $data['name'],
					'weekday' => 'mon',
					'leadtime' => 1,
					'order_frequency' => 1,
					'status' => $data['status']
					);
				$supplier = self::getbyId($data['id'], $this->_country, $this->_table);
				if(!$supplier)
				{
					if($data['status']== 'active')
					{
						self::insert($p_arrParams, $this->_country, $this->_table);
						$arrDataSendEmailNew[] = $p_arrParams; 
					}
				}
				else
				{
					if($data['status']=='inactive')
					{
						self::deletebyId($data['id'], $this->_country, $this->_table);
						$arrDataSendEmailRemove[] = $p_arrParams; 
					}	
				}
			}
		}
		
		$arrResult = array(
			'sendEmailRemove' => $arrDataSendEmailRemove,
			'sendEmailNew' => $arrDataSendEmailNew
			);
		
		return $arrResult;
	}
	
	public static function getbyId($i, $p_Country, $table)
	{
		$db	= Zend_Registry::get('db');
		$strQuery	= 'SELECT * FROM '. $table . ' WHERE id=' . intval($i) . ';';
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public static function getAll($p_Country, $table)
	{
		$db	= Zend_Registry::get('db');
		$strQuery	= 'SELECT * FROM '.$p_Country. '_'. $table . ' Order by name ASC;';
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public static function deletebyId($i, $p_Country, $table)
	{
		$db	= Zend_Registry::get('db');
		$strQuery	= 'DELETE FROM '. $table . ' WHERE id=' . intval($i) . ';';
		$db->execute($strQuery);
		$db->closeConnection();
	}
	
	public function getWeekday()
	{
		$day = CConfig::getNextDay();
		$db	= Zend_Registry::get('db');
		$strQuery	= 'SELECT * FROM '. $this->_table . ' WHERE weekday="' . $day . '"  ';
		
		//Holidays get data for holidays	
		$t=time() + 2*24*60*60; //current add 2 day
		$nextTwoDays = date('Y-m-d',$t);
		$objHolidays = new QHolidays($this->_country);	
		$arrHoliday = $objHolidays->get_by_date($nextTwoDays);
		
		//Add condiction
		if($arrHoliday)
		{
			$strQuery .= 'OR weekday ="' .$arrHoliday['weekday']. '"';
		}
		
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public static function insert($p_arrParams, $p_Country, $table)
	{
		$db	= Zend_Registry::get('db');
		$resultValue =$db->insert($table , $p_arrParams);
		
		$db->closeConnection();
		return $resultValue;
	}
	
	public static function update($p_arrParams, $p_Country, $table)
	{
		$db	= Zend_Registry::get('db');
		$resultValue = $db->update($table, $p_arrParams, "sku=" . "'" .$p_arrParams["sku"] . "'");
		$db->closeConnection();
		return $resultValue;
	}
	
	public function updateField($p_arrParams)
	{
		$db	= Zend_Registry::get('db');
		$resultValue = $db->update($this->_table, $p_arrParams, 'id = '.intval($p_arrParams['id']));
		$db->closeConnection();
		return $resultValue;
	}
	
	private function _whereSearchPreparePO($p_arrParams)
	{
		$strQuery		= null;
		if(isset($p_arrParams['supplier']) && $p_arrParams['supplier'] ){
			$strQuery	= $strQuery.' AND LOWER(h.supplier_id)="'.strtolower($p_arrParams['supplier']).'"';
		};
		
		if(isset($p_arrParams['total_sku']) && $p_arrParams['total_sku'] )
		{
			if($p_arrParams['total_sku'] =='11')
			{
				$strQuery	= $strQuery.' AND d.total_sku > 0 ' ;
			}
			else
			{
				$strQuery	= $strQuery.' AND d.total_sku > ' . $p_arrParams['total_sku'] . ' ' ;
			}
		};
		
		return $strQuery;
	}
	
	public function totalPreparePO($p_arrParams)
	{
		$strQuery	=  'SELECT count(h.id) as total
						FROM ' . $this->_country . '_poheaders h LEFT JOIN 
								(SELECT supplier_id, count(sku) as total_sku, IFNULL(sum(quantity * cost_item),0) as total_amount  
								FROM ' .  $this->_country . '_podetails
								GROUP BY supplier_id) d 
						ON h.supplier_id = d.supplier_id
						Inner join '. $this->_country . '_suppliers s on s.id = h.supplier_id 
						Where 1 = 1 ' ;
		
		$strQuery	= $strQuery. $this->_whereSearchPreparePO($p_arrParams);
		
		$db			= Zend_Registry::get('db');
		$arrResult	= $db->fetchAll($strQuery);
		$db->closeConnection();
		return isset($arrResult[0]['total']) ? $arrResult[0]['total'] : 0;
	}
	
	public function getPreparePO($p_intOffset, $p_mixLimit, $p_arrParams)
	{
		$strQuery	=  'SELECT h.id, h.supplier_id, s.name, h.po_number ,  IFNULL(d.total_sku, 0) as total_sku, 
									IFNULL(d.total_amount, 0) as total_amount, h.logistic_type,
									h.logistic_method, h.logistic_date, h.logistic_eta
						FROM ' . $this->_country . '_poheaders h LEFT JOIN 
								(SELECT supplier_id, count(sku) as total_sku, IFNULL(sum(quantity * cost_item),0) as total_amount  
								FROM ' .  $this->_country . '_podetails
								GROUP BY supplier_id) d 
						ON h.supplier_id = d.supplier_id
						Inner join '. $this->_country . '_suppliers s on s.id = h.supplier_id 
						Where 1 = 1 ';

		$strQuery	= $strQuery. $this->_whereSearchPreparePO($p_arrParams);
		
		$strQuery = $strQuery .  ' Order by s.name ';
		
		if($p_mixLimit!='all'){
			$strQuery	= $strQuery.' LIMIT '.intval($p_intOffset).','.intval($p_mixLimit);
		}
		
		$db	= Zend_Registry::get('db');
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function getPrepareExportPO($p_intOffset, $p_mixLimit, $p_arrParams)
	{
		$strQuery	=  'Select 
						s.name as supplier, "" as shipping_type, "" as agreement,
						h.po_number, "" as grand_total, "" as tax_amount,
						"" as shipping_cost, "" as note, "" as reverse,
						h.logistic_type, h.logistic_method, h.logistic_date,
						h.logistic_eta, "" as inbound_tracking, "" as supplier_contact_name,
						"" as supplier_contact_phone, "" as supplier_contact_email, "" as po_type,
						"" as contract_type, "" as payment_term, "" as vendor_order_number,
						d.sku, d.cost_item, d.item_tax_amount, d.quantity 
						From '. $this->_country . '_poheaders h 
						Inner join '. $this->_country . '_suppliers s on s.id = h.supplier_id 
						Inner join ' . $this->_country .'_podetails d on d.supplier_id =h.supplier_id
						Where 1 = 1 ';

		$strQuery	= $strQuery. $this->_whereSearchPreparePO($p_arrParams);
		
		$strQuery = $strQuery .  ' Order by s.name  ';
		
		if($p_mixLimit!='all'){
			$strQuery	= $strQuery.' LIMIT '.intval($p_intOffset).','.intval($p_mixLimit);
		}
		
		$db	= Zend_Registry::get('db');
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
}
?>